﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.Management;
using System.Reflection;
using System.Security.Permissions;
using Conducido.BizTalkOppSvc.Business.Domain;
using Conducido.BizTalkOppSvc.Services.Contracts.BtsTypes;
using Conducido.BizTalkOppSvc.Services.Contracts.Exceptions;
using Microsoft.BizTalk.ExplorerOM;
using Microsoft.BizTalk.Operations;
using Microsoft.Win32;

namespace Conducido.BizTalkOppSvc.Services.Implementation
{
    internal class BtsQuery
    {
        private readonly BizTalkServerRegistry _bizTalkServerRegistry;
        private readonly BtsCatalogExplorer _btsExplorer;
        private readonly BizTalkOperations _bizTalkOperations;

        //TODO: Wil ik wel zoveel dingen in de constructor?
        public BtsQuery()
        {
            _bizTalkServerRegistry = new BizTalkServerRegistry();
            try
            {
                _bizTalkServerRegistry = GetMgmtServerInfo();
                _bizTalkOperations = new BizTalkOperations(_bizTalkServerRegistry.BizTalkMgmtDb, _bizTalkServerRegistry.BizTalkMgmtDbServerName);
                _btsExplorer = new BtsCatalogExplorer();
                _btsExplorer.ConnectionString = ConnectionString(_bizTalkServerRegistry.BizTalkMgmtDbServerName);
            }
            catch (Exception exception)
            {
                //TODO: Hier ook nog naar kijken.
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception),
                //                       EventLogEntryType.Error, 0x177a);
                throw new BizTalkQueryInitializeException(0x320c8, exception);
            }
        }

        public Collection<BtServiceInstance> GetServiceInstances(string name)
        {
            if (string.IsNullOrEmpty(name))
            {
                name = string.Empty;
            }
            name = name.Trim();
            bool isNameDefined = !string.IsNullOrEmpty(name);
            Collection<BtServiceInstance> collection = new Collection<BtServiceInstance>();
            try
            {
                foreach (Instance serviceInstance in _bizTalkOperations.GetServiceInstances())
                {
                        if (isNameDefined)
                        {
                            if (string.Compare(serviceInstance.ServiceType, name, true, CultureInfo.CurrentCulture) == 0)
                            {
                                collection.Add(BtServiceInstanceFactory(serviceInstance));
                            }
                        }
                        else
                        {
                            collection.Add(BtServiceInstanceFactory(serviceInstance));
                        }
                }
                return collection;
            }
            catch (Exception exception)
            {
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception), EventLogEntryType.Error, 0x177a);
                throw new BizTalkQueryUnexpectedException(0x320d5, exception, new object[] { name });
            }
            
        }

        public Collection<BtReceiveLocation> GetAllReceiveLocations(string application)
        {
            var applicationCollection = _btsExplorer.Applications;
            try
            {
                var btsApp = applicationCollection[application];
                if (btsApp == null) //TODO: Rijp voor refactoring
                {
                    throw new Exception(string.Format("Application not found in BizTalk DB applicationname: {0}",
                                                      application));
                }
                Collection<BtReceiveLocation> btReceiveLocationCollection = GetReceiveLocations(btsApp.ReceivePorts, "");
                return btReceiveLocationCollection;
            }
            catch (Exception ex)
            {
                Debug.WriteLine("Error in GetAllReceiveLocation");
                Debug.WriteLine(ex.Message);
                if (ex.InnerException != null)
                {
                    Debug.WriteLine(ex.InnerException.Message);
                }
                throw new Exception("Error with retrieving applicationdata", ex);
            }
        }

        /// <summary>
        /// Implementation which retrieves all the sendports from a specific application
        /// </summary>
        /// <param name="application"></param>
        /// <returns></returns>
        public Collection<BtSendPort> GetAllSendPorts(string application)
        {
            var applicationCollection = _btsExplorer.Applications;
            try
            {
                var btsApp = applicationCollection[application];
                if (btsApp == null) //TODO: Rijp voor refactoring
                {
                    throw new Exception(string.Format("Application not found in BizTalk DB applicationname: {0}",
                                                      application));
                }
                Collection<BtSendPort> btSendPortCollection = GetSendPorts(btsApp.SendPorts, "");
                return btSendPortCollection;
            }
            catch (Exception ex)
            {
                throw new Exception("Error with retrieving applicationdata", ex);
            }
        }

        private BtServiceInstance BtServiceInstanceFactory(Instance serviceInstance)
        {
            var instance = new BtServiceInstance
                               {
                                   OrchestrationName = serviceInstance.ServiceType,
                                   Guid = serviceInstance.ID,
                                   Status = ConvertToBtInstanceStatus(serviceInstance.InstanceStatus),
                                   Time = serviceInstance.CreationTime,
                                   Error = serviceInstance.ErrorDescription
                               };
            return instance;
        }

        /// <summary>
        /// Converts enum from microsoft.biztalk.operations namespace to our own enun.
        /// If the convert doens't work this function will give the <see cref="BtInstanceStatus.None"/> back.
        /// </summary>
        /// <param name="instanceStatus"></param>
        /// <returns><see cref="BtInstanceStatus"/> enum</returns>
        private BtInstanceStatus ConvertToBtInstanceStatus(InstanceStatus instanceStatus)
        {
            BtInstanceStatus btInstanceStatus = BtInstanceStatus.None;
            BtInstanceStatus.TryParse(instanceStatus.ToString(), true, out btInstanceStatus);
            return btInstanceStatus;
        }

        /// <summary>
        /// Provides complete systemstate of all application and hosts
        /// </summary>
        /// <returns>BizTalk general systemstatus</returns>
        public BtSysStatus SystemStatus()
        {
            BtSysStatus status = new BtSysStatus();

            try
            {
                Collection<BtHost> btHostCollection = GetHosts("");
                Collection<BtReceiveLocation> btReceiveLocationCollection = GetReceiveLocations(_btsExplorer.ReceivePorts, "");
                Collection<BtSendPort> btSendPortCollection = GetSendPorts(_btsExplorer.SendPorts, "");

                status.Hosts = btHostCollection;
                status.Orchestrations = Orchestrations("");
                status.ReceiveLocations = btReceiveLocationCollection;
                status.SendPorts = btSendPortCollection;
                return status;
            }
            catch (Exception exception)
            {
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception), EventLogEntryType.Error, 0x177a);
                throw new BizTalkQueryUnexpectedException(0x320ca, exception);
            }
        }

        /// <summary>
        /// Retrieves status for a specifc <see cref="BtApplication"/>
        /// </summary>
        /// <param name="applicationName"></param>
        /// <returns></returns>
        public BtSysStatus ApplicationStatus(string applicationName)
        {
            if (string.IsNullOrEmpty(applicationName))
            {
                throw new ArgumentNullException("applicationName");
            }
            var status = new BtSysStatus();
            applicationName = applicationName.Trim();
            bool flag = false;

            try
            {
                Collection<BtHost> btHostsCollection = null;
                Collection<BtReceiveLocation> btReceiveLocationCollection = null;
                Collection<BtSendPort> btSendPortCollection = null;
                Collection<BtOrchestration> btOrchestrationCollection = null;

                foreach (Application application in _btsExplorer.Applications)
                {
                    if (string.Compare(application.Name, applicationName, true, CultureInfo.CurrentCulture) == 0)
                    {
                        flag = true;
                        btReceiveLocationCollection = GetReceiveLocations(application.ReceivePorts, "");
                        btSendPortCollection = GetSendPorts(application.SendPorts, "");
                        btOrchestrationCollection = GetOrchestrations(application.Orchestrations, "");
                        break;
                    }
                }
                if (!flag)
                {
                    throw new BizTalkQueryUnexpectedException(0x320e2, new object[] {applicationName});
                }
                status.Hosts = btHostsCollection;
                status.ReceiveLocations = btReceiveLocationCollection;
                status.SendPorts = btSendPortCollection;
                status.Orchestrations = btOrchestrationCollection;
                return status;
            }
            catch (BizTalkQueryUnexpectedException exception)
            {
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception),
                                       //EventLogEntryType.Error, 0x177a);
                throw;
            }
            catch (Exception exception2)
            {
            //    EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception2),
            //                           EventLogEntryType.Error, 0x177a);
                throw new BizTalkQueryUnexpectedException(0x320cb, exception2, new object[] {applicationName});
            }
        }

        /// <summary>
        /// Retrieves BizTalk registry settings.
        /// </summary>
        /// <returns></returns>
        [RegistryPermission(SecurityAction.Demand, Read = @"SOFTWARE\Microsoft\BizTalk Server\3.0\Administration")]
        private static BizTalkServerRegistry GetMgmtServerInfo()
        {
            BizTalkServerRegistry registry = new BizTalkServerRegistry();
            try
            {
                string name = @"SOFTWARE\Microsoft\BizTalk Server\3.0\Administration";
                RegistryKey key = Registry.LocalMachine.OpenSubKey(name);
                if (key == null)
                {
                    throw  new Exception("RegMissing");
                    //throw new BizTalkRegistryMissingException(0x320c7, new object[] { name });
                }
                registry.BizTalkMgmtDbServerName = (string)key.GetValue("MgmtDBName");
                registry.BizTalkMgmtDb = (string)key.GetValue("MgmtDBServer");
                registry.BizTalkMsgBoxDb = RetrieveMessageBoxDbName();
                key.Close();
                key = null;
                
            }
            catch (Exception exception)
            {
                //TODO: Check naar typed exceptions e.d.
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
            return registry;
        }

        private static string RetrieveMessageBoxDbName()
        {
            string _server = string.Empty;
            string _database = string.Empty;
            try
            {
                using (ManagementObjectSearcher searcherGroupSetting = new ManagementObjectSearcher())
                {
                    searcherGroupSetting.Scope = new ManagementScope(@"root\MicrosoftBizTalkServer");
                    searcherGroupSetting.Query = new SelectQuery("select * from MSBTS_MsgBoxSetting");
                    using (
                        ManagementObjectCollection.ManagementObjectEnumerator enumGroupSetting =
                            searcherGroupSetting.Get().GetEnumerator())
                    {
                        List<string> groupSettings = new List<string>();
                        while (enumGroupSetting.MoveNext())
                        {
                            ManagementObject groupSetting = (ManagementObject) enumGroupSetting.Current;
                            _server = groupSetting["MsgBoxDBServerName"] as string;
                            _database = groupSetting["MsgBoxDBName"] as string;

                            return _database;
                            break;
                        }

                    }
                }
                return string.Empty;
            }
            catch(Exception ex)
            {
                throw;
            }
        }

        private Collection<BtReceiveLocation> GetReceiveLocations(ReceivePortCollection rcvPortColl, string name)
        {
            Collection<BtReceiveLocation> collection = new Collection<BtReceiveLocation>();
            bool receiveLocationNameProvided = !string.IsNullOrEmpty(name);
            bool flag2 = false;
            using (SqlConnection connection = CreateSqlConnectionToBizTalkMngtDb())
            {
                try
                {
                    string message = GetMessage("ReceiveLocationSql");
                    connection.Open();
                    foreach (ReceivePort port in rcvPortColl)
                    {
                        foreach (ReceiveLocation location2 in port.ReceiveLocations)
                        {
                            BtReceiveLocation location;
                            if (receiveLocationNameProvided)
                            {
                                if (string.Compare(name, location2.Name, true, CultureInfo.CurrentCulture) != 0)
                                {
                                    continue;
                                }
                                location = new BtReceiveLocation();
                                location.Name = location2.Name;
                                location.Description = location2.Description;
                                location.ReceivePortName = port.Name;
                                location.Status = location2.Enable;
                                location.TwoWay = port.IsTwoWay;
                                location.Application = port.Application.Name;
                                location.IsManaged = IsManagedPipeline(location2.ReceivePipeline.FullName);
                                location.ReceivePipeline = location2.ReceivePipeline.FullName;
                                if (location2.TransportType != null)
                                {
                                    location.TransportType = FixTransportType(location2.TransportType.Name);
                                }
                                if (port.InboundTransforms.Count > 0)
                                {
                                    location.MapName = ((ITransform)port.InboundTransforms[0]).FullName;
                                }
                                location.Address = location2.Address;
                                location.Handler = ReceiveHandlerInfo(location2.ReceiveHandler);
                                SqlCommand command = new SqlCommand(string.Format(CultureInfo.CurrentCulture, "{0}'{1}'", new object[] { message, location2.Name }), connection);
                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    if (reader.HasRows)
                                    {
                                        reader.Read();
                                        location.Guid = reader.GetGuid(0);
                                        location.DateModified = reader.GetDateTime(1);
                                    }
                                    reader.Close();
                                }
                                collection.Add(location);
                                command.Dispose();
                                flag2 = true;
                                break;
                            }
                            location = new BtReceiveLocation();
                            location.Name = location2.Name;
                            location.Description = location2.Description;
                            location.ReceivePortName = port.Name;
                            location.Status = location2.Enable;
                            location.TwoWay = port.IsTwoWay;
                            location.Application = port.Application.Name;
                            location.IsManaged = IsManagedPipeline(location2.ReceivePipeline.FullName);
                            location.ReceivePipeline = location2.ReceivePipeline.FullName;
                            if (location2.TransportType != null)
                            {
                                location.TransportType = FixTransportType(location2.TransportType.Name);
                            }
                            if (port.InboundTransforms.Count > 0)
                            {
                                location.MapName = ((ITransform)port.InboundTransforms[0]).FullName;
                            }
                            location.Address = location2.Address;
                            location.Handler = this.ReceiveHandlerInfo(location2.ReceiveHandler);
                            SqlCommand command2 = new SqlCommand(string.Format(CultureInfo.CurrentCulture, "{0}'{1}'", new object[] { message, location2.Name }), connection);
                            using (SqlDataReader reader2 = command2.ExecuteReader())
                            {
                                if (reader2.HasRows)
                                {
                                    reader2.Read();
                                    location.Guid = reader2.GetGuid(0);
                                    location.DateModified = reader2.GetDateTime(1);
                                }
                                reader2.Close();
                            }
                            collection.Add(location);
                            command2.Dispose();
                        }
                        if (flag2)
                        {
                            break;
                        }
                    }
                    connection.Close();
                    return collection;
                }
                catch (Exception exception)
                {
                    //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                    throw;
                }
                finally
                {
                    if ((connection != null) && (connection.State == ConnectionState.Open))
                    {
                        connection.Close();
                    }
                    if (collection != null)
                    {
                        collection = null;
                    }
                }
            }
        }

        private static string GetMessage(string messageCode)
        {
            try
            {
                //TODO: Dit kan veel mooier.
                string query = ResourceStringLoader.GetMessage(messageCode, "Conducido.BizTalkOppSvc.Services.Implementation.Properties.Resources", Assembly.GetExecutingAssembly());
                Debug.WriteLine("BizTalk SQL Query: " + query);
                return query;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
        }

        /// <summary>
        /// Gives all the host for the BizTalk server.
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private Collection<BtHost> GetHosts(string name)
        {
            var collection = new Collection<BtHost>();
            name = name.Trim();
            try
            {
                BtHost host;
                if (string.IsNullOrEmpty(name))
                {
                    foreach (Host biztalkHost in _btsExplorer.Hosts)
                    {
                        host = HostHelper(biztalkHost.Name); //TODO: HostHelper moet een andere naam krijgen.
                        collection.Add(host);
                    }
                }
                else
                {
                    host = HostHelper(name);
                    collection.Add(host);
                }
                return collection;
            }
            catch (BizTalkQueryUnexpectedException ex)
            {
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), ex), EventLogEntryType.Error, 0x177a);
                throw;
            }
            catch (Exception ex)
            {
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception2), EventLogEntryType.Error, 0x177a);
                throw new BizTalkQueryUnexpectedException(0x320d0, ex, new object[] { name });
            }
        }

        private static string GetMessage(string messageCode, params object[] arguments)
        {
            try
            {
                //TODO: Dit kan veel mooier.
                return ResourceStringLoader.GetMessage(messageCode, "Conducido.BizTalkOppSvc.Services.Implementation.Properties.Resources", Assembly.GetExecutingAssembly(), arguments);
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
        }

        private static bool IsManagedPipeline(string pipelineName)
        {
            if (string.IsNullOrEmpty(pipelineName))
            {
                throw new ArgumentNullException("pipelineName");
            }
            pipelineName = pipelineName.Trim();
            try
            {
                string strB = "";
                if (string.Compare(pipelineName, strB, true, CultureInfo.CurrentCulture) == 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
            
        }

        /// <summary>
        /// Fixes the transport type
        /// </summary>
        /// <param name="transportType"></param>
        /// <returns></returns>
        private static string FixTransportType(string transportType)
        {
            //TODO: Is dit echt nodig?
            try
            {
                if (transportType.StartsWith("MQS", StringComparison.CurrentCultureIgnoreCase))
                {
                    transportType = "MQSeries";
                }
                if ((string.Compare(transportType, "HTTP", true, CultureInfo.CurrentCulture) == 0) || (string.Compare(transportType, "HTTPS", true, CultureInfo.CurrentCulture) == 0))
                {
                    transportType = "WCF-BasicHttp";
                }
                return transportType;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
        }

        [SqlClientPermission(SecurityAction.Demand, AllowBlankPassword = true, KeyRestrictions = "", KeyRestrictionBehavior = KeyRestrictionBehavior.AllowOnly)]
        private Collection<BtSendPort> GetSendPorts(SendPortCollection sendPortColl, string name)
        {
            Collection<BtSendPort> collection2;
            Collection<BtSendPort> collection = new Collection<BtSendPort>();
            bool flag = !string.IsNullOrEmpty(name);
            try
            {
                string message = GetMessage("SendPortSql");
                using (SqlConnection connection = CreateSqlConnectionToBizTalkMngtDb())
                {
                    connection.Open();
                    foreach (SendPort port2 in sendPortColl)
                    {
                        BtSendPort port;
                        if (flag)
                        {
                            if (string.Compare(name, port2.Name, true, CultureInfo.CurrentCulture) != 0)
                            {
                                continue;
                            }
                            port = new BtSendPort();
                            port.Name = port2.Name;
                            port.Pipeline = port2.SendPipeline.FullName;
                            port.IsManaged = IsManagedPipeline(port2.SendPipeline.FullName);
                            port.Description = port2.Description;
                            port.TwoWay = port2.IsTwoWay;
                            port.Filter = port2.Filter;
                            port.ApplicationName = port2.Application.Name;
                            port.Status = port2.Status.ToString();
                            port.RouteFailed = port2.RouteFailedMessage;
                            if (port2.PrimaryTransport != null)
                            {
                                port.TransportType = FixTransportType(port2.PrimaryTransport.TransportType.Name);
                                port.Address = port2.PrimaryTransport.Address;
                                port.Handler = this.SendHandlerInfo(port2.PrimaryTransport.SendHandler);
                            }
                            SqlCommand command = new SqlCommand(string.Format(CultureInfo.CurrentCulture, "{0}'{1}'", new object[] { message, port2.Name }), connection);
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    reader.Read();
                                    port.Guid = reader.GetGuid(0);
                                    port.DateModified = reader.GetDateTime(1);
                                }
                                reader.Close();
                            }
                            collection.Add(port);
                            command.Dispose();
                            break;
                        }
                        port = new BtSendPort();
                        port.Name = port2.Name;
                        port.Pipeline = port2.SendPipeline.FullName;
                        port.IsManaged = IsManagedPipeline(port2.SendPipeline.FullName);
                        port.Description = port2.Description;
                        port.Filter = port2.Filter;
                        port.ApplicationName = port2.Application.Name;
                        port.Status = port2.Status.ToString();
                        port.RouteFailed = port2.RouteFailedMessage;
                        port.TwoWay = port2.IsTwoWay;
                        if (port2.PrimaryTransport != null)
                        {
                            port.TransportType = FixTransportType(port2.PrimaryTransport.TransportType.Name);
                            port.Address = port2.PrimaryTransport.Address;
                            port.Handler = this.SendHandlerInfo(port2.PrimaryTransport.SendHandler);
                        }
                        SqlCommand command2 = new SqlCommand(string.Format(CultureInfo.CurrentCulture, "{0}'{1}'", new object[] { message, port2.Name }), connection);
                        using (SqlDataReader reader2 = command2.ExecuteReader())
                        {
                            if (reader2.HasRows)
                            {
                                reader2.Read();
                                port.Guid = reader2.GetGuid(0);
                                port.DateModified = reader2.GetDateTime(1);
                            }
                            reader2.Close();
                        }
                        collection.Add(port);
                        command2.Dispose();
                    }
                    connection.Close();
                }
                collection2 = collection;
            }
            catch (Exception exception)
            {
                //TODO: Fix this.
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
            finally
            {
                if (collection != null)
                {
                    collection = null;
                }
            }
            return collection2;
        }

        private BtSendHandler SendHandlerInfo(SendHandler handler)
        {
            BtSendHandler handler3;
            if (handler == null)
            {
                throw new ArgumentNullException("handler");
            }
            try
            {
                BtSendHandler handler2 = new BtSendHandler();
                handler2.ProtocolName = handler.TransportType.Name;
                handler2.Host = this.HostHelper(handler.Host.Name);
                handler3 = handler2;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
            return handler3;
        }

        [SqlClientPermission(SecurityAction.Demand, AllowBlankPassword = true, KeyRestrictions = "", KeyRestrictionBehavior = KeyRestrictionBehavior.AllowOnly)]
        private BtHost HostHelper(string hostName)
        {
            SqlCommand command = null;
            try
            {
                BtHost host = new BtHost();
                string message = GetMessage("HostInformationSql", new object[] { hostName });
                using (SqlConnection connection = CreateSqlConnectionToBizTalkMngtDb())
                {
                    connection.Open();
                    command = new SqlCommand(message, connection);
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        BtHostInstance instance;
                        host.Name = hostName;
                        host.HostInstances = new Collection<BtHostInstance>();
                        while (reader.Read())
                        {
                            instance = new BtHostInstance();
                            instance.HostInstanceName = reader.GetString(0);
                            instance.DateModified = reader.GetDateTime(1);
                            instance.Guid = reader.GetGuid(2);
                            instance.ServerName = reader.GetString(4);
                            host.HostInstances.Add(instance);
                        }
                        reader.Close();
                        for (int i = 0; i < host.HostInstances.Count; i++)
                        {
                            instance = host.HostInstances[i];
                            BtHostInstance instance2 = host.HostInstances[i];
                            instance.Status = HostStatus(this.IsHostInstanceStarted(hostName, instance2.Guid));
                            host.HostInstances[i] = instance;
                        }
                    }
                    connection.Close();
                }
                return host;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="orchColl"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        private static Collection<BtOrchestration> GetOrchestrations(BtsOrchestrationCollection orchColl, string name)
        {
            var collection = new Collection<BtOrchestration>();
            try
            {
                foreach (BtsOrchestration foundOrchestration in orchColl)
                {
                    BtOrchestration orchestration;
                    if (!string.IsNullOrEmpty(name))
                    {
                        if (string.Compare(name, foundOrchestration.FullName, true, CultureInfo.CurrentCulture) != 0)
                        {
                            continue;
                        }
                        orchestration = new BtOrchestration();
                        orchestration.Application = foundOrchestration.Application.Name;
                        orchestration.Name = foundOrchestration.FullName;
                        orchestration.Status = foundOrchestration.Status.ToString();
                        collection.Add(orchestration);
                        break;
                    }
                    orchestration = new BtOrchestration();
                    orchestration.Application = foundOrchestration.Application.Name;
                    orchestration.Name = foundOrchestration.FullName;
                    orchestration.Status = foundOrchestration.Status.ToString();
                    collection.Add(orchestration);
                }
                return collection;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
        }

        [SqlClientPermission(SecurityAction.Demand, AllowBlankPassword = true, KeyRestrictions = "", KeyRestrictionBehavior = KeyRestrictionBehavior.AllowOnly)]
        private bool IsHostInstanceStarted(string hostName, Guid hostInstanceGuid)
        {
            bool isHostInstanceStarted = false;
            SqlCommand command = null;
            try
            {
                string message = GetMessage("HeartbeatSql", new object[] { hostInstanceGuid });
                if (hostName.Contains("Isolated"))
                {
                    return true;
                }
                using (SqlConnection connection = CreateSqlConnectionToBizTalkMsgBoxDb())
                {
                    connection.Open();
                    command = new SqlCommand(message, connection);
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        isHostInstanceStarted = true;
                    }
                    reader.Close();
                    connection.Close();
                }
                return isHostInstanceStarted;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                }
            }
        }

        private BtReceiveHandler ReceiveHandlerInfo(ReceiveHandler handler)
        {
            if (handler == null)
            {
                throw new ArgumentNullException("handler");
            }
            try
            {
                var btReceiveHandler = new BtReceiveHandler();
                btReceiveHandler.ProtocolName = handler.TransportType.Name;
                btReceiveHandler.Host = this.HostHelper(handler.Host.Name);
                return btReceiveHandler;
            }
            catch (Exception exception)
            {
                //EventLogger.Write(MethodBase.GetCurrentMethod(), exception);
                throw;
            }
        }

        /// <summary>
        /// Gets an orchestration by name
        /// No name gives all orchestrations.
        /// </summary>
        /// <param name="orchestrationName"></param>
        /// <returns></returns>
        private Collection<BtOrchestration> Orchestrations(string orchestrationName)
        {
            if (string.IsNullOrEmpty(orchestrationName))
            {
                orchestrationName = string.Empty;
            }
            orchestrationName = orchestrationName.Trim();
            bool orchestrationNameDefined = !string.IsNullOrEmpty(orchestrationName);
            bool stopLooping = false;
            Collection<BtOrchestration> collection = new Collection<BtOrchestration>();
            try
            {
                foreach (Application application in _btsExplorer.Applications)
                {
                    foreach (BtOrchestration orchestration in GetOrchestrations(application.Orchestrations, orchestrationName))
                    {
                        if (orchestrationNameDefined)
                        {
                            collection.Add(orchestration);
                            stopLooping = true;
                            break;
                        }
                        collection.Add(orchestration);
                    }
                    if (stopLooping)
                    {
                        break;
                    }
                }
                return collection;
            }
            catch (BizTalkQueryUnexpectedException exception)
            {
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception), EventLogEntryType.Error, 0x177a);
                throw;
            }
            catch (Exception exception2)
            {
                //EventLogger.LogMessage(EventFormatter.FormatEvent(MethodBase.GetCurrentMethod(), exception2), EventLogEntryType.Error, 0x177a);
                throw new BizTalkQueryUnexpectedException(0x320ce, exception2, new object[] { orchestrationName });
            }
        }

        private static string HostStatus(bool enabled)
        {
            if (!enabled)
            {
                return "Stopped";
            }
            return "Started";
        }

        /// <summary>
        /// Creates the SqlConnection
        /// </summary>
        /// <returns></returns>
        private SqlConnection CreateSqlConnectionToBizTalkMngtDb()
        {
            string connectionString = ConnectionString(_bizTalkServerRegistry.BizTalkMgmtDbServerName);
            Debug.WriteLine("BizTalk Connectionstring: " + connectionString);
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// Creates the SQLConnection to 
        /// </summary>
        /// <returns></returns>
        private SqlConnection CreateSqlConnectionToBizTalkMsgBoxDb()
        {
            string connectionString = ConnectionString(_bizTalkServerRegistry.BizTalkMsgBoxDb);
            Debug.WriteLine("BizTalk Connectionstring: " + connectionString);
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// Creates connectionstring from registry
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private string ConnectionString(string dbName)
        {
            try
            {
                return "Data Source=" + _bizTalkServerRegistry.BizTalkMgmtDb + ";Initial Catalog=" + dbName + ";Integrated Security=SSPI;Connect Timeout=120";
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}